﻿CREATE PROCEDURE [SSIS].[GetSolutionEnvironment](
	@EnvironmentName sysname = NULL,
	@SolutionFolderName sysname,
	@ProjectName sysname,
	@ReferenceID bigint OUTPUT
)
AS
BEGIN
	SELECT
		@ReferenceID = ref.[reference_id]
	FROM [$(SSISDB)].[catalog].[environment_references] AS ref
	INNER JOIN [$(SSISDB)].[catalog].[projects] AS prj
		ON (prj.[project_id] = ref.[project_id])
		AND(prj.[name] = @ProjectName)
	INNER JOIN [$(SSISDB)].[catalog].[folders] AS fld
		ON (fld.[folder_id] = prj.[folder_id])
		AND(fld.[name] = @SolutionFolderName)
	WHERE (ref.[environment_name] = ISNULL(@EnvironmentName,ref.[environment_name]));
END
GO
EXECUTE sp_addextendedproperty @name = N'Description', @value = N'This procedure will get the SSIS environment reference ID for the specified project.', @level0type = N'SCHEMA', @level0name = N'SSIS', @level1type = N'PROCEDURE', @level1name = N'GetSolutionEnvironment';

